
CREATE     function f_finmstraderdtl(
    @bnyear integer,
    @bnmonth integer,
    @enyear integer,
    @enmonth integer,
    @nsublevels integer,
    @traderid integer,
    @filid integer
)
returns @local_tab table(
    nyear integer,
    nmonth integer,
    subid integer,
    subcode varchar(40),
    moneyid integer,
    bmbala decimal(18,2),
    bysumdebit decimal(18,2),
    bysumcredit decimal(18,2),
    tmsumdebit decimal(18,2),
    tmsumcredit decimal(18,2),
    tmsum decimal(18,2),
    fbmbala decimal(18,2),
    fbysumdebit decimal(18,2),
    fbysumcredit decimal(18,2),
    ftmsumdebit decimal(18,2),
    ftmsumcredit decimal(18,2),
    ftmsum decimal(18,2)
)
as
begin
  declare @sc1len integer
  declare @sc2len integer
  declare @sc3len integer
  declare @sc4len integer
  declare @sc5len integer
  declare @sc6len integer
  declare @sc7len integer
  declare @sc8len integer
  declare @sc9len integer
  declare @sc10len integer

  declare @subjectid integer
  declare @nisdebit integer
  declare @nbalyear integer
  declare @nbalmonth integer
  declare @iyear integer
  declare @imonth integer
  declare @ssubcode varchar(40)
 
  declare
    @nyear integer,
    @nmonth integer,
    @subid integer,
    @subcode varchar(40),
    @moneyid integer,
    @bmbala decimal(18,2),
    @bysumdebit decimal(18,2),
    @bysumcredit decimal(18,2),
    @tmsumdebit decimal(18,2),
    @tmsumcredit decimal(18,2),
    @tmsum decimal(18,2),
    @fbmbala decimal(18,2),
    @fbysumdebit decimal(18,2),
    @fbysumcredit decimal(18,2),
    @ftmsumdebit decimal(18,2),
    @ftmsumcredit decimal(18,2),
    @ftmsum decimal(18,2)

  select @sc1len=sc1len,@sc2len=sc2len,@sc3len=sc3len,
         @sc4len=sc4len,@sc5len=sc5len,@sc6len=sc6len,
         @sc7len=sc7len,@sc8len=sc8len,@sc9len=sc9len,
         @sc10len=sc10len
  from dbo.f_finsublen()    

  declare local_cursor cursor local
  for select subjectid,subcode,isdebit,m.moneyid 
  from tfd_subject s left join td_money m on 1=1
  where dotrader=1  and subnode=1
  order by subjectid
  open local_cursor
  fetch next from local_cursor into @subjectid,@ssubcode,@nisdebit,@moneyid
  while @@fetch_status = 0
  begin
    set @iyear=@bnyear
    set @imonth=@bnmonth
    set @nbalyear = null 
    while (@iyear<@enyear or (@iyear=@enyear and @imonth<=@enmonth))
    begin
      set @nisdebit=1
      select @nbalyear=nbalyear,@nbalmonth=nbalmonth,@bmbala=bmbala,@bysumcredit=bysumcredit,@bysumdebit=bysumdebit,
        @tmsumcredit=tmsumcredit,@tmsumdebit=tmsumdebit,@tmsum=tmsum,
        @fbmbala=fbmbala,@fbysumcredit=fbysumcredit,@fbysumdebit=fbysumdebit,
        @ftmsumcredit=ftmsumcredit,@ftmsumdebit=ftmsumdebit,@ftmsum=ftmsum
      from dbo.f_mstradergetpriorinfo(@moneyid,@traderid,@subjectid,@iyear,@imonth,@filid)

      set @nyear=@iyear
      set @nmonth=@imonth

      if (@nbalyear is not null and @nbalmonth is not null)
      begin
        if (@nbalyear<>@iyear or @nbalmonth<>@imonth)  
        begin
          if (@nisdebit=1) 
            set @bmbala=@bmbala+@tmsumdebit-@tmsumcredit
          else
            set @bmbala=@bmbala-@tmsumdebit+@tmsumcredit

          set @bysumdebit=@bysumdebit+@tmsumdebit
          set @bysumcredit=@bysumcredit+@tmsumcredit

            if (@nbalyear<>@iyear)
            begin
              set @bysumdebit =0
              set @bysumcredit=0 
            end 
          set @tmsumdebit=0
          set @tmsumcredit=0
          set @tmsum=0

          if (@nisdebit=1) 
            set @fbmbala=@fbmbala+@ftmsumdebit-@ftmsumcredit
          else
            set @fbmbala=@fbmbala-@ftmsumdebit+@ftmsumcredit

          set @fbysumdebit=@fbysumdebit+@ftmsumdebit
          set @fbysumcredit=@fbysumcredit+@ftmsumcredit

            if (@nbalyear<>@iyear)
            begin
              set @fbysumdebit=0
              set @fbysumcredit=0 
            end 
          set @ftmsumdebit=0
          set @ftmsumcredit=0
          set @ftmsum=0
        end
        if (len(@ssubcode)>=@sc10len)
        begin
          set @subcode=substring(@ssubcode,1,@sc10len)
          insert into @local_tab(
            nyear ,
            nmonth,
            subid,
            subcode,
            moneyid,
            bmbala,
            bysumdebit,
            bysumcredit,
            tmsumdebit,
            tmsumcredit,
            tmsum,
            fbmbala,
            fbysumdebit,
            fbysumcredit,
            ftmsumdebit,
            ftmsumcredit,
            ftmsum
          ) values(
            @nyear ,
            @nmonth,
            @subid,
            @subcode,
            @moneyid,
            @bmbala,
            @bysumdebit,
            @bysumcredit,
            @tmsumdebit,
            @tmsumcredit,
            @tmsum,
            @fbmbala,
            @fbysumdebit,
            @fbysumcredit,
            @ftmsumdebit,
            @ftmsumcredit,
            @ftmsum
          )
        end
        if (len(@ssubcode)>=@sc9len)
        begin
          set @subcode=substring(@ssubcode,1,@sc9len)
          insert into @local_tab(
            nyear ,
            nmonth,
            subid,
            subcode,
            moneyid,
            bmbala,
            bysumdebit,
            bysumcredit,
            tmsumdebit,
            tmsumcredit,
            tmsum,
            fbmbala,
            fbysumdebit,
            fbysumcredit,
            ftmsumdebit,
            ftmsumcredit,
            ftmsum
          ) values(
            @nyear ,
            @nmonth,
            @subid,
            @subcode,
            @moneyid,
            @bmbala,
            @bysumdebit,
            @bysumcredit,
            @tmsumdebit,
            @tmsumcredit,
            @tmsum,
            @fbmbala,
            @fbysumdebit,
            @fbysumcredit,
            @ftmsumdebit,
            @ftmsumcredit,
            @ftmsum
          )
        end
        if (len(@ssubcode)>=@sc8len)
        begin
          set @subcode=substring(@ssubcode,1,@sc8len)
          insert into @local_tab(
            nyear ,
            nmonth,
            subid,
            subcode,
            moneyid,
            bmbala,
            bysumdebit,
            bysumcredit,
            tmsumdebit,
            tmsumcredit,
            tmsum,
            fbmbala,
            fbysumdebit,
            fbysumcredit,
            ftmsumdebit,
            ftmsumcredit,
            ftmsum
          ) values(
            @nyear ,
            @nmonth,
            @subid,
            @subcode,
            @moneyid,
            @bmbala,
            @bysumdebit,
            @bysumcredit,
            @tmsumdebit,
            @tmsumcredit,
            @tmsum,
            @fbmbala,
            @fbysumdebit,
            @fbysumcredit,
            @ftmsumdebit,
            @ftmsumcredit,
            @ftmsum
          )
        end
        if (len(@ssubcode)>=@sc7len)
        begin
          set @subcode=substring(@ssubcode,1,@sc7len)
          insert into @local_tab(
            nyear ,
            nmonth,
            subid,
            subcode,
            moneyid,
            bmbala,
            bysumdebit,
            bysumcredit,
            tmsumdebit,
            tmsumcredit,
            tmsum,
            fbmbala,
            fbysumdebit,
            fbysumcredit,
            ftmsumdebit,
            ftmsumcredit,
            ftmsum
          ) values(
            @nyear ,
            @nmonth,
            @subid,
            @subcode,
            @moneyid,
            @bmbala,
            @bysumdebit,
            @bysumcredit,
            @tmsumdebit,
            @tmsumcredit,
            @tmsum,
            @fbmbala,
            @fbysumdebit,
            @fbysumcredit,
            @ftmsumdebit,
            @ftmsumcredit,
            @ftmsum
          )
        end
        if (len(@ssubcode)>=@sc6len)
        begin
          set @subcode=substring(@ssubcode,1,@sc6len)
          insert into @local_tab(
            nyear ,
            nmonth,
            subid,
            subcode,
            moneyid,
            bmbala,
            bysumdebit,
            bysumcredit,
            tmsumdebit,
            tmsumcredit,
            tmsum,
            fbmbala,
            fbysumdebit,
            fbysumcredit,
            ftmsumdebit,
            ftmsumcredit,
            ftmsum
          ) values(
            @nyear ,
            @nmonth,
            @subid,
            @subcode,
            @moneyid,
            @bmbala,
            @bysumdebit,
            @bysumcredit,
            @tmsumdebit,
            @tmsumcredit,
            @tmsum,
            @fbmbala,
            @fbysumdebit,
            @fbysumcredit,
            @ftmsumdebit,
            @ftmsumcredit,
            @ftmsum
          )
        end
        if (len(@ssubcode)>=@sc5len) 
        begin
          set @subcode=substring(@ssubcode,1,@sc5len)
          insert into @local_tab(
            nyear ,
            nmonth,
            subid,
            subcode,
            moneyid,
            bmbala,
            bysumdebit,
            bysumcredit,
            tmsumdebit,
            tmsumcredit,
            tmsum,
            fbmbala,
            fbysumdebit,
            fbysumcredit,
            ftmsumdebit,
            ftmsumcredit,
            ftmsum
          ) values(
            @nyear ,
            @nmonth,
            @subid,
            @subcode,
            @moneyid,
            @bmbala,
            @bysumdebit,
            @bysumcredit,
            @tmsumdebit,
            @tmsumcredit,
            @tmsum,
            @fbmbala,
            @fbysumdebit,
            @fbysumcredit,
            @ftmsumdebit,
            @ftmsumcredit,
            @ftmsum
          )
        end

        if (len(@ssubcode)>=@sc4len) 
        begin
          set @subcode=substring(@ssubcode,1,@sc4len)
          insert into @local_tab(
            nyear ,
            nmonth,
            subid,
            subcode,
            moneyid,
            bmbala,
            bysumdebit,
            bysumcredit,
            tmsumdebit,
            tmsumcredit,
            tmsum,
            fbmbala,
            fbysumdebit,
            fbysumcredit,
            ftmsumdebit,
            ftmsumcredit,
            ftmsum
          ) values(
            @nyear ,
            @nmonth,
            @subid,
            @subcode,
            @moneyid,
            @bmbala,
            @bysumdebit,
            @bysumcredit,
            @tmsumdebit,
            @tmsumcredit,
            @tmsum,
            @fbmbala,
            @fbysumdebit,
            @fbysumcredit,
            @ftmsumdebit,
            @ftmsumcredit,
            @ftmsum
          )
        end

        if (len(@ssubcode)>=@sc3len) 
        begin
          set @subcode=substring(@ssubcode,1,@sc3len)
          insert into @local_tab(
            nyear ,
            nmonth,
            subid,
            subcode,
            moneyid,
            bmbala,
            bysumdebit,
            bysumcredit,
            tmsumdebit,
            tmsumcredit,
            tmsum,
            fbmbala,
            fbysumdebit,
            fbysumcredit,
            ftmsumdebit,
            ftmsumcredit,
            ftmsum
          ) values(
            @nyear ,
            @nmonth,
            @subid,
            @subcode,
            @moneyid,
            @bmbala,
            @bysumdebit,
            @bysumcredit,
            @tmsumdebit,
            @tmsumcredit,
            @tmsum,
   @fbmbala,
            @fbysumdebit,
            @fbysumcredit,
            @ftmsumdebit,
            @ftmsumcredit,
            @ftmsum
          )
        end

        if (len(@ssubcode)>=@sc2len) 
        begin
          set @subcode=substring(@ssubcode,1,@sc2len)
          insert into @local_tab(
            nyear ,
            nmonth,
            subid,
            subcode,
            moneyid,
            bmbala,
            bysumdebit,
            bysumcredit,
            tmsumdebit,
            tmsumcredit,
            tmsum,
            fbmbala,
            fbysumdebit,
            fbysumcredit,
            ftmsumdebit,
            ftmsumcredit,
            ftmsum
          ) values(
            @nyear ,
            @nmonth,
            @subid,
            @subcode,
            @moneyid,
            @bmbala,
            @bysumdebit,
            @bysumcredit,
            @tmsumdebit,
            @tmsumcredit,
            @tmsum,
            @fbmbala,
            @fbysumdebit,
            @fbysumcredit,
            @ftmsumdebit,
            @ftmsumcredit,
            @ftmsum
          )
        end

        if (len(@ssubcode)>=@sc1len) 
        begin
          set @subcode=substring(@ssubcode,1,@sc1len)
          insert into @local_tab(
            nyear ,
            nmonth,
            subid,
            subcode,
            moneyid,
            bmbala,
            bysumdebit,
            bysumcredit,
            tmsumdebit,
            tmsumcredit,
            tmsum,
            fbmbala,
            fbysumdebit,
            fbysumcredit,
            ftmsumdebit,
            ftmsumcredit,
            ftmsum
          ) values(
            @nyear ,
            @nmonth,
            @subid,
            @subcode,
            @moneyid,
            @bmbala,
            @bysumdebit,
            @bysumcredit,
            @tmsumdebit,
            @tmsumcredit,
            @tmsum,
            @fbmbala,
            @fbysumdebit,
            @fbysumcredit,
            @ftmsumdebit,
            @ftmsumcredit,
            @ftmsum
          )
        end
      end

      set @imonth=@imonth+1
      if (@imonth>12) 
      begin
        set @iyear=@iyear+1
        set @imonth=1
      end
    end
   
    fetch next from local_cursor into @subjectid,@ssubcode,@nisdebit,@moneyid
  end
  close local_cursor
  deallocate local_cursor
  return
end





